package com.example.demotest;


import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.nodes.Node;
import org.jsoup.nodes.TextNode;
import org.jsoup.select.Elements;

import java.io.File;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


public class Main {

    public final static String path = "/media/chenglin/新加卷/temp3/136_1011.html";
    public final static String url = "jdbc:postgresql://localhost:5432/test";
    public final static String user = "postgres";
    public final static String password = "postgres";

    public static void main(String[] args) {

        try {

            Document document = Jsoup.parse(new File(path), "utf-8");
            Elements slowestElement = document.select("#slowest-individual-queries-table tbody tr");
            List<Map<String, Object>> objects = new ArrayList<>();
            if (slowestElement != null) {
                int size = slowestElement.size();
                System.out.println("size:" + size);
                slowestElement.parallelStream().forEach(
                        element -> {
                            Map<String, Object> map = new LinkedHashMap<>();
                            List<Node> nodes = element.getAllElements().get(0).childNodes();
                            String index = ((Element) nodes.get(1)).text();
                            String cost = ((Element) nodes.get(3)).text().replaceAll("ms", "");
                            String sql = ((Element) nodes.get(5)).select("td div").text();
                            Element pre = ((Element) nodes.get(5)).select("td pre").get(0);
                            List<Node> nodes1 = pre.childNodes();

                            map.put("index", index);

                            int sec = 0;
                            if (cost.contains("s") && !cost.contains("m")) {
                                sec = Integer.valueOf(cost.substring(0, cost.indexOf("s")));
                            } else {
                                if (cost.contains("m") && !cost.contains("h")) {
                                    int minute = Integer.valueOf(cost.substring(0, cost.indexOf("m"))) * 60;
                                    sec = minute + sec;
                                } else if (cost.contains("h")) {
                                    int minute = Integer.valueOf(cost.substring(cost.indexOf("h") + 1, cost.indexOf("m"))) * 60;
                                    sec = minute + sec;
                                    int hour = Integer.valueOf(cost.substring(0, cost.indexOf("h"))) * 3600;
                                    sec = hour + sec;
                                }
                            }

                            map.put("cost", sec);
                            map.put("sql", sql);

                            String date = ((Element) nodes1.get(1)).text().replaceAll(":", "");
                            String sequence1 = " - ";
                            String dateValue = ((TextNode) nodes1.get(2)).text().substring(1).replace(sequence1, "");

                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            Date dateLong = null;
                            try {
                                dateLong = sdf.parse(dateValue);
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            map.put(date, dateLong);

                            String database = ((Element) nodes1.get(3)).text().replaceAll(":", "");
                            String databaseValue = ((TextNode) nodes1.get(4)).text().substring(1).replace(sequence1, "");
                            map.put(database, databaseValue);

                            String user = ((Element) nodes1.get(5)).text().replaceAll(":", "");
                            String userValue = ((TextNode) nodes1.get(6)).text().substring(1).replace(sequence1, "");
                            map.put(user, userValue);

                            String remote = ((Element) nodes1.get(7)).text().replaceAll(":", "");
                            String remoteValue = ((TextNode) nodes1.get(8)).text().substring(1).replace(sequence1, "");
                            map.put(remote, remoteValue);

                            String application = ((Element) nodes1.get(9)).text().replaceAll(":", "");
                            String applicationValue = ((TextNode) nodes1.get(10)).text().substring(1).replace(" ]", "");
                            map.put(application, applicationValue);
                            objects.add(map);
                        }
                );
                saveDb(objects);

            } else {
                System.out.println("size:" + 0);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("end");
    }

    public static void saveDb(List<Map<String, Object>> maps) {
        PreparedStatement stmt = null;
        java.sql.Connection conn = null;

        try {

            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("Database connected");
            conn.setAutoCommit(Boolean.FALSE);

            for (Map<String, Object> item : maps) {

                if (item == null || item.get("index") == null) {
                    continue;
                }

                String sql = "insert into test2 values (?,?,?,?,?,?,?,?)";

                stmt = conn.prepareStatement(sql);
                stmt.setString(1, (String) item.get("index"));
                stmt.setInt(2, (Integer) item.get("cost"));
                stmt.setString(3, (String) item.get("sql"));
                stmt.setTimestamp(4, localDateTimeToDate(((Date) item.get("Date"))));
                stmt.setString(5, (String) item.get("Database"));
                stmt.setString(6, (String) item.get("User"));
                stmt.setString(7, (String) item.get("Remote"));
                stmt.setString(8, (String) item.get("Application"));
                System.out.println("sql:" + ((PreparedStatement) stmt).toString());
                stmt.executeUpdate();
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static Timestamp localDateTimeToDate(Date localDateTime) {
        if (localDateTime == null) {
            return null;
        }
        try {
            Timestamp date = new Timestamp(localDateTime.getTime());
            return date;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
